import datetime
import json

import pymysql
from common.settings import logger
from typing import Union, List


class MysqlHandler:

    def __init__(self, host: str, port: int, user: str, password: str, database: str):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = 'utf8mb4'
        self.connection = None
        self.cursor = None

    def connect(self):
        self.connection = pymysql.connect(host=self.host,
                                          port=self.port,
                                          user=self.user,
                                          password=self.password,
                                          database=self.database,
                                          charset=self.charset,
                                          cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.connection.cursor()

    def close(self):
        self.cursor.close()
        self.connection.close()

    def get_one(self, sql, params=None) -> dict:
        logger.info(f'执行get_one方法，sql语句：{sql}，参数：{params}')
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            return result
        except Exception as e:
            logger.error(e)
        finally:
            self.close()

    def get_all(self, sql, params=None) -> List[dict]:
        logger.info(f'执行get_all方法，sql语句：{sql}，参数：{params}')
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            logger.error(e)
        finally:
            self.close()

    def insert(self, sql, params=None) -> int:
        logger.info(f'执行insert方法，sql语句：{sql}，参数：{params}')
        return self.execute(sql, params, 'insert')

    def update(self, sql, params=None) -> None:
        logger.info(f'执行update方法，sql语句：{sql}，参数：{params}')
        return self.execute(sql, params, 'update')

    def delete(self, sql, params=None) -> None:
        logger.info(f'执行delete方法，sql语句：{sql}，参数：{params}')
        return self.execute(sql, params, 'delete')

    def execute(self, sql, params, sql_type) -> Union[int, None]:
        try:
            self.connect()
            rows = self.cursor.execute(sql, params)
            logger.info(f'执行sql后，受影响的行数：{rows}')
            insert_id = None
            if sql_type == 'insert':
                insert_id = self.connection.insert_id()
                logger.info(f'新增记录的id：{insert_id}')
            self.connection.commit()
            return insert_id
        except Exception as e:
            self.connection.rollback()
            logger.error(e)
        finally:
            self.close()


if __name__ == '__main__':
    mysql_util = MysqlHandler(host='127.0.0.1', port=3306, user='root', password='123456', database='authority-dev-q')
    # sql2 = "insert into book(title, price, market_price, pub, is_active) values (%s, %s, %s, %s, %s)"
    # params2 = ('大学物理', 40, 50, '某高校出版社', 1)
    # res = mysql_util.insert(sql2, params2)
    # print(res)
    sql2 = "select * from uac_app where id = 1"
    res = mysql_util.get_one(sql2)
    print(res)
    print(type(res))
    for k, v in res.items():
        if isinstance(res[k], datetime.datetime):
            res[k] = str(res[k])
    print(res)
    # 存储到excel中需要转换成字符串
    print(json.dumps(res, ensure_ascii=False))

